import pymysql

## 数据库选择和安装
'''
pip3 install pymysql -i https://mirrors.aliyun.com/pypi/simple
yum install -y mariadb mariadb-server # 关系型数据库
systemctl start mariadb
mysqladmin password "123456"
mysql -uroot -p123456 -e "create database python default charset 'utf8'"
create table user(id int, name varchar(20), age int, gender enum('男','女'));
'''


## python中的异常处理
'''
try:
    10 / 2
except ValueError as e:
    print(e)
    print("程序错误")
except Exception as e:
    print("程序存在未知错误")
else:
    print("程序正常执行，没有任何错误")
finally:
    print("进程关闭，程序结束")
'''

## 事务的四个特性
'''
原子性
隔离性
一致性
持续性
'''

## sql的类别
'''
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言
DDL 数据定义语言 
'''
# conn=pymysql.connect(host="127.0.0.1", user="root", password="root", database="python", charset="utf8")
# # 
# cursor= conn.cursor() # 创建游标 
# sql = "insert into user(id, name, age,gender) values(1, '张三', 20, '男')"
# cursor.execute(sql) # 执行sql语句
# conn.commit() # 提交事务

class Database:
    charset = "utf8"
    port = 3306
    def __init__(self, host = "127.0.0.1", user="root",passwd=None,database=None) -> None:
        self.host = host
        self.user = user
        self.passwd = passwd
        self.database = database
        # 获取mysql的连接以及游标对象
        self.conn = conn = pymysql.connect(
            host=self.host, 
            user=self.user,
            passwd=self.passwd,
            database=self.database,
            port=self.port,
            charset=self.charset
        ) 
        self.cursor = conn.cursor() # 创建游标

    # 插入数据
    def create(self, sql, condition=None):
        try:
            if condition and len(condition) == 1:
                result = self.cursor.execute(sql, condition)
            else:
                result = self.cursor.executemany(sql, condition)
            self.conn.commit()
            print(f"成功插入{result}行数据")
        except Exception as e:
            self.conn.rollback()
            print("插入错误，数据回滚", e.args)

    # 查询数据
    def read(self, sql, condition=None, data_lines=None):
        result = self.cursor.execute(sql, condition)
        if not data_lines:
            data = self.cursor.fetchall()
        else:
            data = self.cursor.fetchmany(data_lines)
        print(f"查询出{result}行数据")
        for k in data:
            print(*k)  # 打印查询结果

    # 更新数据
    def update(self, sql, condition=None):
        try:
            result = self.cursor.execute(sql, condition)
            self.conn.commit()
            print(f"成功更新{result}行数据")
        except Exception as e:
            self.conn.rollback()
            print("更新错误，数据回滚", e.args)

    # 删除数据
    def delete(self, sql, condition=None):
        try:
            result = self.cursor.execute(sql, condition)
            self.conn.commit()
            print(f"成功删除{result}行数据")
        except Exception as e:
            self.conn.rollback()
            print("删除错误，数据回滚", e.args)

if __name__ == "__main__": # 测试
    db = Database(passwd="root", database="python")
    # """
    # 插入数据
    insert = '''
    insert into user(id, name,age,gender)values(%s,%s,%s,%s) '''
    db.create(sql=insert, condition=((2,"小花",22,"女"),(3,"小李",15,"女")))
#  """
    
    """
    # 查询数据
    select = '''
        select * from user where id < %s
        '''
    db.read(sql=select, condition=3)
    """

    """
    # 更新数据
    update = '''
    update user set name=%s where id=%s
    '''
    db.update(sql=update, condition=("小王", 1))
    """

    """
    # 删除数据
    delete = '''
    delete from user where id=%s
    '''
    db.delete(sql=delete, condition=1)
    """
